<?
//------------------------------------------------------------------------------------------------------------------
function get_signups_days(){
        global $session_db_name,$dbh;

	 $global_start = date("U");
        $days = Array();
        for($i=0;$i<=30;$i++){
                $days[$i] = date("Y-m-d H:i:s",mktime(0,0,0,date("m"),date("d")-$i,date("Y")));
        }
        foreach($days as $d)  $global_days[$d]  = "";

    $SQL="select 
	   count(distinct r.reseller) as count,
	   count(distinct c.id) as amount,
	   year(r.date) y, month(r.date) m,dayofmonth(r.date)  d, date_add(date_add( date_add(r.date,interval  - second(r.date)  second) , interval - minute(r.date) minute),interval - hour(r.date) hour) as created
	 from 
	   resellers r left join sales c on (r.id=c.id) 
	 where  r.date>date_add(now(),interval -31 day) group by y,m,d";
    if (!($result = @mysql_db_query($session_db_name, $SQL, $dbh)))
                        return "ERROR: $SQL";
    //exit($SQL);    
    while($row = @mysql_fetch_array($result)){
                $global_days[$row[created]][count] =  $row[count];
		$global_days[$row[created]][amount] =  $row[amount];
	        //$global_days["$row[y]-$row[m]-$row[d]"][count] =  $row[count];
	        //$global_days["$row[y]-$row[m]-$row[d]"][amount] =  $row[amount];
	
	        $global_count += $row[count];
		$global_amount += $row[amount];

        }
        $p = 0;
        while(list($key,$gd) = each($global_days)){
                if($p%2 == 0) $color = "bgcolor='#DDDDDD'";
                else $color = "bgcolor='#EEEEEE'";

                if($gd[count]) $list .= "<tr $color onMouseover=this.style.backgroundColor='#ffbb88' onMouseout=this.style.backgroundColor=''><td>$p</td><td>".expl($key,'d',0)."</td><td align='center'>".paymnet_link($key,'d',0,$gd[amount])."</td><td align='right'>$gd[count]</td></tr>";
                else           $list .= "<tr $color onMouseover=this.style.backgroundColor='#ffbb88' onMouseout=this.style.backgroundColor=''><td>$p</td><td>".expl($key,'d',0)."</td><td align='center'>0</td><td align='right'>0</td></tr>";
                $p++;
        }
        $global_stop = date("U");
        $global_time = $global_stop - $global_start;

	$list .= "<tr><td colspan=2>Total</td><td colspan='' align='center'> $global_amount </td><td colspan='' align='right'> $global_count </td></tr>";
        $list .= "<tr><td colspan='20' align='right'>Finished for : $global_time seconds</td></tr>";

        return $list;
}
//-------------------------------------------------------------------------------------
function get_signups_hours(){
        global $session_db_name,$dbh;
	
	 $global_start = date("U");
        $hours = Array();
        for($i=0;$i<=23;$i++){
                $hours[$i] = date("Y-m-d H:i:s",mktime(date("H")-$i,0,0,date("m"),date("d"),date("Y")));
        }
        foreach($hours as $d)  $global_hours[$d]  = "";

        $SQL="select 
	       count(distinct r.reseller) as count,
	       count(distinct c.id) as amount,
	       year(r.date) y, month(r.date) m,dayofmonth(r.date)  d,hour(r.date) h, date_add( date_add(r.date,interval  - second(r.date)  second) , interval - minute(r.date) minute) as created
	       from 
	       resellers r left join sales c on (r.id=c.id) 
	      where  r.date>date_add(now(),interval -1 day) group by y,m,d,h";
    
    if (!($result = @mysql_db_query($session_db_name, $SQL, $dbh)))
                        return $SQL;
        while($row = @mysql_fetch_array($result)){
		$global_hours[$row[created]][count] =  $row[count];
		$global_hours[$row[created]][amount] =  $row[amount];
		$global_count += $row[count];
		$global_amount += $row[amount];
        }
        $p = 0;
        while(list($key,$gd) = each($global_hours)){
                if($p%2 == 0) $color = "bgcolor='#DDDDDD'";
                else $color = "bgcolor='#EEEEEE'";

                if($gd[count]) $list .= "<tr $color onMouseover=this.style.backgroundColor='#ffbb88' onMouseout=this.style.backgroundColor=''><td>$p</td><td>".expl($key,'h',1)."</td><td align='center'>".paymnet_link($key,'h',0,$gd[amount])."</td><td align='right'>$gd[count]</td></tr>";
                else         $list .= "<tr $color onMouseover=this.style.backgroundColor='#ffbb88' onMouseout=this.style.backgroundColor=''><td>$p</td><td>".expl($key,'h',1)."</td><td align='center'>0</td><td align='right'>0</td></tr>";
                $p++;
        }
        $global_stop = date("U");
        $global_time = $global_stop - $global_start;

	$list .= "<tr><td colspan=2>Total</td><td colspan='' align='center'> $global_amount </td><td colspan='' align='right'> $global_count </td></tr>";
        $list .= "<tr><td colspan='20' align='right'>Finished for : $global_time seconds</td></tr>";

        return $list;
}
//------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------
function get_signups_months(){
        global $session_db_name,$dbh;

	 $global_start = date("U");
        $months = Array();
        for($i=0;$i<=23;$i++){
                $months[$i] = date("Y-m-d H:i:s",mktime(0,0,0,date("m")-$i,'01',date("Y")));
        }
	$global_months = Array();
        foreach($months as $d)  $global_months[$d]  = "";
    $SQL="select 
	      count(distinct r.reseller) as count,
	      count(distinct c.id) as amount,
	      year(r.date) y, month(r.date) m,date_add(date_add(date_add(date_add(r.date,interval  - second(r.date)  second) , interval - minute(r.date) minute),interval - hour(r.date) hour),interval - (dayofmonth(r.date)-1) day) as creat 
	   
	    from 
	      resellers r left join sales c on (r.id=c.id) 
	      where  r.date>date_add(now(),interval -1 year) group by y,m";
    
        if (!($result = @mysql_db_query($session_db_name, $SQL, $dbh)))
                        return "ERROR:$SQL";	
        while($row = @mysql_fetch_array($result)){
                $global_months[$row[creat]][count] =  $row[count];
		$global_months[$row[creat]][amount] =  $row[amount];
		$global_count += $row[count];
		$global_amount += $row[amount];
        }
        $p = 0;
        while(list($key,$gd) = each($global_months)){
                if($p%2 == 0) $color = "bgcolor='#DDDDDD'";
                else $color = "bgcolor='#EEEEEE'";

                if($gd[count]) $list .= "<tr $color onMouseover=this.style.backgroundColor='#ffbb88' onMouseout=this.style.backgroundColor=''><td>$p</td><td>".expl($key,'m',0)."</td><td  align='center'><a class='links'  href=>".paymnet_link($key,'m',0,$gd[amount])."</td><td align='right'>$gd[count]</td></tr>";
                else         $list .= "<tr $color onMouseover=this.style.backgroundColor='#ffbb88' onMouseout=this.style.backgroundColor=''><td>$p</td><td>".expl($key,'m',0)."</td><td align='center'>0</td><td align='right'>0</td></tr>";
                $p++;
        }
        $global_stop = date("U");
        $global_time = $global_stop - $global_start;

	$list .= "<tr><td colspan=2>Total</td><td colspan='' align='center'> $global_amount </td><td colspan='' align='right'> $global_count </td></tr>";
        $list .= "<tr><td colspan='20' align='right'>Finished for : $global_time seconds</td></tr>";

        return $list;
}
//------------------------------------------------------------------------------------------------------------------
function expl($date,$t,$type=0){
	global $sid;
        $arr = explode(" ",$date);
        return "<a target='_blank'  class='links' href='detailed.php?s=$sid&date=$date&type=$t'>".$arr[$type]."</a>";
}
//------------------------------------------------------------------------------------------------------------------
function paymnet_link($date,$t,$type,$amount){
        global $sid;
        return "<a target='_blank'  class='links' href='detailed.php?s=$sid&date=$date&type=$t&payment=true'>".$amount."</a>";
}
//------------------------------------------------------------------------------------------------------------------
function detailed_report($date,$type,$payment=''){
        global $session_db_name,$dbh,$sid,$id;

	 $global_start = date("U");
        $users = Array();
        switch($type){
                case "h":
                        $tdate = " date_add('$date',interval 1 hour) ";
                break;
                case "d":
                         $tdate = " date_add('$date',interval 1 day) ";
                break;
                case "m":
                         $tdate = " date_add('$date',interval 1 month) ";
                break;
        }

	if($payment){
	        $SQL = "select sum(c.real_price) as amount,r.reseller,r.date,r.id,r.firstname,r.country,r.email,r.ip,r.state,rf.referer from resellers r,sales c left join referers rf on(rf.id = r.id and rf.type='signup')  where r.id = c.id and c.state='normal' and r.date > '$date' and r.date < $tdate group by c.id order by r.date desc";
	}
	else{
	        //$SQL = "select username,homedir,sum(created),uid,gid,quota,flags,fullname,country,email,ip,blocked from systemdata where created > '$date' and created < $tdate group by username order by created desc";
	        $SQL = "select r.reseller,r.date,r.id,r.email, r.country,r.firstname,r.lastname,r.ip,rf.referer  from resellers r left join referers rf on(rf.id = r.id and rf.type='signup') where r.date > '$date' and r.date < $tdate  group by r.reseller order by r.date";
	//exit($SQL);	
	}
        if (!($result = @mysql_db_query($session_db_name, $SQL, $dbh)))
                        return "ERROR : $SQL";
    $p=1;
    $res="";
    while($row = @mysql_fetch_array($result)){
	$res.=detailed_report_html($row,$p++);
    }
    $global_stop = date("U");
    $global_time = $global_stop - $global_start;
    $res .= "<tr><td colspan='20' align='right'>Finished for : $global_time seconds</td></tr>";
    
    return $res;
}
function detailed_report_html($row,$p){
//print_r($row);
    global $sid,$baseURL;
    //$p++;
    if(!$row) return;
    if($p%2 == 0) $color = "bgcolor='#DDDDDD'";
    else $color = "bgcolor='#EEEEEE'";
    $rounded=round($row[amount],3);
    if($row[amount]) $row[amount] = "<br><font color='red'>( $ $rounded )</font>";
    else $row[amount] = "";
    $params[sid]=$sid;
    $params[action]="set_reseller";
    $params[resellername]=$row[reseller];
    $url="$baseURL/admin/?".encode_params($params);
    $list .="
	      <tr $color onMouseover=this.style.backgroundColor='#ffbb88' onMouseout=this.style.backgroundColor=''>
	      <td> $p </td>
	      <td> <a target='_blank'  class='links'  href='$url'> $row[reseller] $row[amount]</a></td>
	      <td> $row[date]</td>
	      <td> $row[id]</td>
	      <td> $row[firstname] $row[lastname]</td>
	      <td> <a class='links' href='mailto:$row[email]'>$row[email]</a></td>
	      <td> $row[ip]</td>
	      <td>&nbsp;".$row[referer]."</td>
	      </tr>
	      ";
    
    return $list;
    
}
?>
